﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class CourseDataProvider
    {

        GetNextID getID = new GetNextID();

        ConnectDB con = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;
    //Add course
        public int AddCourse(string ID, string name, string start, int accept)
        {
            string query = string.Format("insert into Courses(ID,NameCourse,StartDate,AcceptReigister,NewRecord,CreateDateTime)" +
                " values('{0}','{1}','{2}',{3},{4},'{5}')", ID, name, DateTime.Parse(start), accept,1, DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }
    //Load danh sach tat ca cac khoa hoc
        public DataSet loadCourses()
        {
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand("Select * from Courses where AcceptReigister = 1", cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
    //Load thong tin cua 1 khoa hoc
        public SqlDataReader loadCourseInfo(string idCourse)
        {
            string query = string.Format("Select * from Courses where ID ='{0}'", idCourse);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            SqlDataReader sdr = cmd.ExecuteReader();
            return sdr;
        }
    //Load danh sach khoa hoc cua sinh vien
        public DataSet loadCoursesOfStudent(string Id)
        {
            string query = string.Format("Select DISTINCT Courses.ID, Courses.NameCourse from Students," +
                "StudentInClass, Classes, ClassInCourse, Courses where Students.ID = '{0}' and "+
                "Students.ID = StudentInClass.IDStudent "+
                "and StudentInClass.IDClass = ClassInCourse.IDClass and ClassInCourse.IDCourse = Courses.ID", Id);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
    //Load danh sach cac khoa hoc giong voi khoa hoc hien tai
        public DataSet loadCourseSame(string idCourse, string[] idSub)
        {
            string query = string.Format("Select DISTINCT Courses.ID, Courses.NameCourse from Courses,SubjectInCourse " +
                "where Courses.ID != '{0}' and Courses.ID = SubjectInCourse.IDCourse and (", idCourse);
            for (int i = 0; i < idSub.Length; i++)
            {
                string id = idSub[i];
                if(i == idSub.Length - 1)
                    query += string.Format("SubjectInCourse.IDSub = '{0}')", id);
                else
                    query += string.Format("SubjectInCourse.IDSub = '{0}' or ", id);
            }
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }

    //Chỉnh sửa thông tin khóa học
        public int ChangeInfoCourse(string id, string name, string start, string accept, string note)
        {
            Service.RegisterService gettime = new Service.RegisterService();
            string currtime = gettime.currentTime();
            string query = string.Format("update Courses set NameCourse = '{1}', StartDate = '{2}', AcceptReigister = {3}, " +
                "Note = '{4}', NewRecord = 2, LastUpdate = '{5}' where ID = '{0}'", id, name, DateTime.Parse(start), accept, note, DateTime.Parse(currtime));
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

    // Convert to xml string
        public string ConvertInfoCourseToXml(string idCourse)
        {
            string query = string.Format("select * from Courses where ID = '{0}' for xml auto,elements", idCourse);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }
        //Lay Id cua record co cot Newrecord = 1
        //Lay Id cua record vua insert vao table
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select ID from Courses where NewRecord = 1");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update Courses set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay Id cua record vua insert vao table
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select ID from Courses where NewRecord = 2");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update Courses set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 
    }
}
